-- =============================================
-- 登录失败限制表
-- 用于记录用户登录失败次数和锁定状态
-- 创建日期: 2024年
-- 部署位置: UUM用户下
-- =============================================

-- 创建登录失败记录表
CREATE TABLE UUM.LOGIN_FAILURE_LOG (
    ID NUMBER(20) NOT NULL,
    USER_NAME VARCHAR2(128) NOT NULL,
    FAILURE_TIME DATE NOT NULL,
    IP_ADDRESS VARCHAR2(50),
    USER_AGENT VARCHAR2(1000),
    CREATED_AT DATE DEFAULT SYSDATE,
    CONSTRAINT PK_LOGIN_FAILURE_LOG PRIMARY KEY (ID)
)
TABLESPACE UUM_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
    INITIAL 64K
    NEXT 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
);

-- 添加表注释
COMMENT ON TABLE UUM.LOGIN_FAILURE_LOG IS '登录失败记录表';
COMMENT ON COLUMN UUM.LOGIN_FAILURE_LOG.ID IS '主键ID';
COMMENT ON COLUMN UUM.LOGIN_FAILURE_LOG.USER_NAME IS '用户名';
COMMENT ON COLUMN UUM.LOGIN_FAILURE_LOG.FAILURE_TIME IS '失败时间';
COMMENT ON COLUMN UUM.LOGIN_FAILURE_LOG.IP_ADDRESS IS 'IP地址';
COMMENT ON COLUMN UUM.LOGIN_FAILURE_LOG.USER_AGENT IS '用户代理';
COMMENT ON COLUMN UUM.LOGIN_FAILURE_LOG.CREATED_AT IS '创建时间';

-- 创建用户名索引
CREATE INDEX UUM.IX_LOGIN_FAILURE_LOG_USERNAME ON UUM.LOGIN_FAILURE_LOG (USER_NAME)
TABLESPACE UUM_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
    INITIAL 256K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
);

-- 创建失败时间索引
CREATE INDEX UUM.IX_LOGIN_FAILURE_LOG_TIME ON UUM.LOGIN_FAILURE_LOG (FAILURE_TIME)
TABLESPACE UUM_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
    INITIAL 256K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
);

-- 创建用户锁定状态表
CREATE TABLE UUM.USER_LOCK_STATUS (
    ID NUMBER(20) NOT NULL,
    USER_NAME VARCHAR2(128) NOT NULL,
    IS_LOCKED NUMBER(1) DEFAULT 0 NOT NULL,
    LOCK_TIME DATE,
    UNLOCK_TIME DATE,
    FAILURE_COUNT NUMBER(10) DEFAULT 0 NOT NULL,
    LAST_FAILURE_TIME DATE,
    CREATED_AT DATE DEFAULT SYSDATE,
    UPDATED_AT DATE DEFAULT SYSDATE,
    CONSTRAINT PK_USER_LOCK_STATUS PRIMARY KEY (ID),
    CONSTRAINT UK_USER_LOCK_STATUS_USERNAME UNIQUE (USER_NAME),
    CONSTRAINT CK_USER_LOCK_STATUS_LOCKED CHECK (IS_LOCKED IN (0, 1))
)
TABLESPACE UUM_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
    INITIAL 64K
    NEXT 8K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
);

-- 添加表注释
COMMENT ON TABLE UUM.USER_LOCK_STATUS IS '用户锁定状态表';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.ID IS '主键ID';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.USER_NAME IS '用户名';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.IS_LOCKED IS '是否锁定(0:未锁定,1:已锁定)';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.LOCK_TIME IS '锁定时间';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.UNLOCK_TIME IS '解锁时间';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.FAILURE_COUNT IS '失败次数';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.LAST_FAILURE_TIME IS '最后失败时间';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.CREATED_AT IS '创建时间';
COMMENT ON COLUMN UUM.USER_LOCK_STATUS.UPDATED_AT IS '更新时间';

-- 创建序列
CREATE SEQUENCE UUM.SEQ_LOGIN_FAILURE_LOG
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

CREATE SEQUENCE UUM.SEQ_USER_LOCK_STATUS
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- 创建触发器自动设置ID
CREATE OR REPLACE TRIGGER UUM.TRG_LOGIN_FAILURE_LOG_ID
BEFORE INSERT ON UUM.LOGIN_FAILURE_LOG
FOR EACH ROW
BEGIN
    SELECT UUM.SEQ_LOGIN_FAILURE_LOG.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER UUM.TRG_USER_LOCK_STATUS_ID
BEFORE INSERT ON UUM.USER_LOCK_STATUS
FOR EACH ROW
BEGIN
    SELECT UUM.SEQ_USER_LOCK_STATUS.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

-- 创建更新时间触发器
CREATE OR REPLACE TRIGGER UUM.TRG_USER_LOCK_STATUS_UPDATE
BEFORE UPDATE ON UUM.USER_LOCK_STATUS
FOR EACH ROW
BEGIN
    :NEW.UPDATED_AT := SYSDATE;
END;
/

-- 创建清理过期登录失败记录的存储过程
CREATE OR REPLACE PROCEDURE UUM.CLEAN_LOGIN_FAILURE_LOG(
    p_days_to_keep IN NUMBER DEFAULT 30
)
AS
BEGIN
    DELETE FROM UUM.LOGIN_FAILURE_LOG 
    WHERE FAILURE_TIME < SYSDATE - p_days_to_keep;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('清理完成，删除了 ' || SQL%ROWCOUNT || ' 条过期记录');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 创建自动解锁过期用户的存储过程
CREATE OR REPLACE PROCEDURE UUM.AUTO_UNLOCK_EXPIRED_USERS
AS
BEGIN
    UPDATE UUM.USER_LOCK_STATUS 
    SET IS_LOCKED = 0,
        UNLOCK_TIME = SYSDATE,
        UPDATED_AT = SYSDATE
    WHERE IS_LOCKED = 1 
      AND LOCK_TIME IS NOT NULL 
      AND LOCK_TIME + INTERVAL '10' MINUTE < SYSDATE;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('自动解锁完成，解锁了 ' || SQL%ROWCOUNT || ' 个用户');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 授权给相关用户（根据实际情况调整）
-- GRANT SELECT, INSERT, UPDATE, DELETE ON UUM.LOGIN_FAILURE_LOG TO your_app_user;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON UUM.USER_LOCK_STATUS TO your_app_user;
-- GRANT EXECUTE ON UUM.CLEAN_LOGIN_FAILURE_LOG TO your_app_user;
-- GRANT EXECUTE ON UUM.AUTO_UNLOCK_EXPIRED_USERS TO your_app_user;

-- 创建定时任务清理过期数据（可选）
-- BEGIN
--     DBMS_SCHEDULER.CREATE_JOB (
--         job_name        => 'UUM.CLEAN_LOGIN_FAILURE_JOB',
--         job_type        => 'PLSQL_BLOCK',
--         job_action      => 'BEGIN UUM.CLEAN_LOGIN_FAILURE_LOG(30); END;',
--         start_date      => SYSTIMESTAMP,
--         repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
--         enabled         => TRUE,
--         comments        => '每天凌晨2点清理30天前的登录失败记录'
--     );
-- END;
-- /

-- 创建定时任务自动解锁（可选）
-- BEGIN
--     DBMS_SCHEDULER.CREATE_JOB (
--         job_name        => 'UUM.AUTO_UNLOCK_JOB',
--         job_type        => 'PLSQL_BLOCK',
--         job_action      => 'BEGIN UUM.AUTO_UNLOCK_EXPIRED_USERS; END;',
--         start_date      => SYSTIMESTAMP,
--         repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
--         enabled         => TRUE,
--         comments        => '每分钟检查并自动解锁过期用户'
--     );
-- END;
-- /